﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using System.Data.OleDb;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Common;

namespace FromAccToSql
{
    public partial class frmTransfare : Form
    {
        public frmTransfare()
        {
            InitializeComponent();
        }

        #region "Proprties"

        public string Server { get; set; }
        public string Database { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public string ErrorMessage { get; set; }
        public string SqlCnnString { get; set; }

        #endregion

        #region "Controls Events"
        private void btnBrowseFile_Click(object sender, EventArgs e)
        {
            openAccFile.ShowDialog();
        }

        private void openAccFile_FileOk(object sender, CancelEventArgs e)
        {
            this.txtAccessFile.Text = openAccFile.FileName;
            this.btnTestCnn.Enabled = true;
        }

        private void btnTestCnn_Click(object sender, EventArgs e)
        {
            lblStatus.Text = "";
            lblStatus.Refresh();

            this.btnTestCnn.Text = "Wait ...";
            this.btnTestCnn.Refresh();
            string errMessage = "";
            Server = txtServer.Text.Trim();
            txtServer.Text = Server;

            Database = txtDatabase.Text.Trim();
            txtDatabase.Text = Database;

            UserName = txtUsername.Text.Trim();
            txtUsername.Text = UserName;

            Password = txtPassword.Text;

            frmMessageBox msgBox = new frmMessageBox();

            if (TestConnection(Server, Database, UserName, Password, out errMessage))
            {
                btnTransfer.Enabled = true;
            }
            else
            {
                btnTransfer.Enabled = false;
            }

            ErrorMessage = errMessage;
            msgBox.Show(this);
            this.btnTestCnn.Text = "Test Connection";
            this.btnTestCnn.Refresh();
        }

        private bool TestConnection(string sServer, string sDB, string sDBUser, string sDBPwd, out string sMsg)
        {
            SqlConnection cnn;
            var sBuilder = new StringBuilder();

            if (this.cboAuth.SelectedIndex == 0)
            {
                sBuilder.Append("Data Source=");
                sBuilder.Append(sServer);
                sBuilder.Append(";Initial Catalog=");
                sBuilder.Append(sDB);
                sBuilder.Append(";Integrated Security=True");
            }
            else
            {
                sBuilder.Append("Data Source={SQL Server Native Client 10.0};Server=");
                sBuilder.Append(sServer);
                sBuilder.Append(";Database=");
                sBuilder.Append(sDB);
                sBuilder.Append(";UID=");
                sBuilder.Append(sDBUser);
                sBuilder.Append(";pwd=");
                sBuilder.Append(sDBPwd);
                sBuilder.Append(";MultipleActiveResultSets=True");
            }

            SqlCnnString = sBuilder.ToString();
            
            cnn = new SqlConnection(sBuilder.ToString());

            try
            {
                cnn.Open();

                if (cnn.State == ConnectionState.Open)
                {
                    sMsg = "Test connection succeeded.";
                    cnn.Close();
                    cnn = null;
                    return true;
                }
                else
                {
                    sMsg = "";
                    return false;
                }
            }
            catch (InvalidOperationException)
            {
                sMsg = "Test connection isn't succeeded. " + "Please check DataSource or DataBase Name.";
                cnn = null;
                return false;
            }
            catch (SqlException ex)
            {
                if (ex.Number == 18487 || ex.Number == 18488)
                {
                    sMsg = "Test connection isn't succeeded. " + "Specified password has expired or incorrect.";
                }
                else
                {
                    sMsg = "Test connection isn't succeeded. " + ex.Message;
                }
                cnn = null;
                return false;

            }
        }

        private void btnTransfer_Click(object sender, EventArgs e)
        {
            try
            {
                lblStatus.Text = "Please wait ...";
                lblStatus.Refresh();

                var ds = new DataSet();
                OleDbConnection oleConn = GetOleDbConnection();

                var daModule = new OleDbDataAdapter("SELECT * From [Module];", oleConn);
                daModule.Fill(ds, "Module");

                var daModuleParameter = new OleDbDataAdapter("SELECT Mod_Par.Module_Id, p.Name, Mod_Par.Nr, p.Param_Value, p.Param_String, p.Unit" +
                    " FROM Param as p INNER JOIN Mod_Par ON p.Param_Id = Mod_Par.Param_Id ORDER BY Mod_Par.Module_Id, Mod_Par.Nr;", oleConn);
                daModuleParameter.Fill(ds, "ModuleParameter");

                var daModuleType = new OleDbDataAdapter("SELECT ModType_Id, Name, Description From Modtype", oleConn);
                daModuleType.Fill(ds, "ModuleType");

                var daModuleTypeParameter = new OleDbDataAdapter("SELECT Modt_Par.ModType_Id, p.Name, Modt_Par.Param_Nr, p.Param_Value, p.Param_String, p.Unit" +
                    " FROM Param as p INNER JOIN Modt_Par ON p.Param_Id = Modt_Par.Param_Id ORDER BY Modt_Par.ModType_Id, Modt_Par.Param_Nr;", oleConn);
                daModuleTypeParameter.Fill(ds, "ModuleTypeParameter");

                var daChannelType = new OleDbDataAdapter("SELECT Chtype_Id, Basetype, Description, Unit From Chtype;", oleConn);
                daChannelType.Fill(ds, "ChannelType");

                var daChannelAddress = new OleDbDataAdapter("SELECT Chadr_Id, Port, Drive, Unit, Subunit, AdrByte, AdrBit, Inverted, Hi, Low, WordLength, On_Error, AsyncType From Chadr;", oleConn);
                daChannelAddress.Fill(ds, "ChannelAddress");

                var daChannel = new OleDbDataAdapter("SELECT [Channel_Id], [Chadr_Id], [Chtype_Id], [Name], [Description], [Used], [Interval], [OrdNr], [Transtype]," +
                    " [Factor], [Offset], [Operation], [SaveStat], [Sign], [Interval1] From [Channel];", oleConn);
                daChannel.Fill(ds, "Channel");

                var daBase_Ch = new OleDbDataAdapter("SELECT [Channel], [Base_Ch], [Neg] From [Base_Ch];", oleConn);
                daBase_Ch.Fill(ds, "Base_Ch");

                var daModuleType_ChannelType = new OleDbDataAdapter("SELECT Modtype_Id, Chtype_Id, Nr, Name FROM Modt_Cht;", oleConn);
                daModuleType_ChannelType.Fill(ds, "ModuleType_ChannelType");

                var daModule_Channel = new OleDbDataAdapter("SELECT Module_Id, Channel_Id, Nr FROM Mod_Ch;", oleConn);
                daModule_Channel.Fill(ds, "Module_Channel");

                oleConn.Close();
                DataTable dtModule = ds.Tables[0];
                DataTable dtModuleParameter = ds.Tables[1];
                DataTable dtModuleType = ds.Tables[2];
                DataTable dtModuleTypeParameter = ds.Tables[3];
                DataTable dtChannelType = ds.Tables[4];
                DataTable dtChannelAddress = ds.Tables[5];
                DataTable dtChannel = ds.Tables[6];
                DataTable dtBase_Ch = ds.Tables[7];
                DataTable dtModuleType_ChannelType = ds.Tables[8];
                DataTable dtModule_Channel = ds.Tables[9];

                var sqlConn = GetSqlConnection();
                //ModuleType
                foreach (DataRow rows in dtModuleType.Rows)
                {
                    PDT.InsertModuleType(sqlConn, rows["ModType_Id"].ToString(), rows["Name"].ToString(), rows["Description"].ToString());
                }
                //Module
                PDT.SetIdentityInsertOnOff(sqlConn, "Module", true);

                foreach (DataRow rows in dtModule.Rows)
                {
                    PDT.InsertModule(sqlConn, rows["Module_Id"].ToString(), rows["ModType_Id"].ToString(), rows["Name"].ToString(),
                                                         rows["Description"].ToString(), rows["Used"].ToString(), rows["Config"].ToString());
                }

                PDT.SetIdentityInsertOnOff(sqlConn, "Module", false);
                //ModuleParameter
                foreach (DataRow rows in dtModuleParameter.Rows)
                {
                    PDT.InsertModuleParameter(sqlConn, rows["Module_Id"].ToString(), rows["Name"].ToString(), rows["Nr"].ToString(),
                                                         rows["Param_Value"].ToString(), rows["Param_String"].ToString(), rows["Unit"].ToString());
                }
                //ModuleTypeParameter
                foreach (DataRow rows in dtModuleTypeParameter.Rows)
                {

                    PDT.InsertModuleTypeParameter(sqlConn, rows["ModType_Id"].ToString(), rows["Name"].ToString(), rows["Param_Nr"].ToString(),
                                                         rows["Param_Value"].ToString(), rows["Param_String"].ToString(), rows["Unit"].ToString());
                }
                //ChannelType
                foreach (DataRow rows in dtChannelType.Rows)
                {

                    PDT.InsertChannelType(sqlConn, rows["Chtype_Id"].ToString(), rows["Basetype"].ToString(), rows["Description"].ToString(), rows["Unit"].ToString());
                }
                //ChannelAddress
                PDT.SetIdentityInsertOnOff(sqlConn, "ChannelAddress", true);
                foreach (DataRow rows in dtChannelAddress.Rows)
                {
                    PDT.InsertChannelAddress(sqlConn, rows["Chadr_Id"].ToString(), rows["Port"].ToString(), rows["Drive"].ToString(), rows["Unit"].ToString(),
                        rows["Subunit"].ToString(), rows["AdrByte"].ToString(), rows["AdrBit"].ToString(), rows["Inverted"].ToString(),
                        rows["Hi"].ToString(), rows["Low"].ToString(), rows["WordLength"].ToString(), rows["On_Error"].ToString(), rows["AsyncType"].ToString());
                }
                PDT.SetIdentityInsertOnOff(sqlConn, "ChannelAddress", false);
                //Channel
                PDT.SetIdentityInsertOnOff(sqlConn, "Channel", true);
                foreach (DataRow rows in dtChannel.Rows)
                {
                    PDT.InsertChannel(sqlConn, rows["Channel_Id"].ToString(), rows["Chadr_Id"].ToString(), rows["Chtype_Id"].ToString(), rows["Name"].ToString(),
                        rows["Description"].ToString(), rows["Used"].ToString(), rows["Interval"].ToString(), rows["OrdNr"].ToString(), rows["Transtype"].ToString(),
                        rows["Factor"].ToString(), rows["Offset"].ToString(), rows["Operation"].ToString(), rows["SaveStat"].ToString() , rows["Sign"].ToString(),
                        rows["Interval1"].ToString());
                }
                PDT.SetIdentityInsertOnOff(sqlConn, "Channel", false);
                //dtBase_Ch
                foreach (DataRow rows in dtBase_Ch.Rows)
                {
                    PDT.InsertBase_Ch(sqlConn, rows["Channel"].ToString(), rows["Base_Ch"].ToString(), rows["Neg"].ToString());
                }
                //ModuleType_ChannelType
                foreach (DataRow rows in dtModuleType_ChannelType.Rows)
                {
                    PDT.InsertModuleType_ChannelType(sqlConn, rows["Modtype_Id"].ToString(), rows["Chtype_Id"].ToString(), rows["Nr"].ToString(), rows["Name"].ToString());
                }
                //Module_Channel
                foreach (DataRow rows in dtModule_Channel.Rows)
                {
                    PDT.InsertModule_Channel(sqlConn, rows["Module_Id"].ToString(), rows["Channel_Id"].ToString(), rows["Nr"].ToString());
                }

                sqlConn.Close();

                lblStatus.Text = "Transfer completed";
            }
            catch
            {
                lblStatus.Text = "Transfer completed with errors!";
                lblStatus.ForeColor = Color.Red;
            } 
            lblStatus.Refresh();
        }
 
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cboAuth.SelectedIndex == 0)
            {
                txtUsername.Enabled = false;
                txtPassword.Enabled = false;
            }
            else
            {
                txtUsername.Enabled = true;
                txtPassword.Enabled = true;
            }
        }
        #endregion

        #region "Helper function"
        private SqlConnection GetSqlConnection()
        {
            SqlConnection cnn = new SqlConnection(SqlCnnString);
            try
            {
                 cnn.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return cnn;
        }

        private OleDbConnection GetOleDbConnection()
        {
            OleDbConnection conn = new OleDbConnection();
            try
            {
                String connectionString = @"Provider=Microsoft.JET.OlEDB.4.0;"
               + @"Data Source=" + txtAccessFile.Text;
                conn = new OleDbConnection(connectionString);
                conn.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return conn;
        }
        #endregion


    }
}
